<?php
class ModelCatalogCategory extends Model {
	public function getCategory($category_id) {
		$query = $this->db->query("SELECT DISTINCT * FROM " . DB_PREFIX . "category c LEFT JOIN " . DB_PREFIX . "category_description cd ON (c.category_id = cd.category_id) LEFT JOIN " . DB_PREFIX . "category_to_store c2s ON (c.category_id = c2s.category_id) WHERE c.category_id = '" . (int)$category_id . "' AND cd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND c2s.store_id = '" . (int)$this->config->get('config_store_id') . "' AND c.status = '1'");
		
		return $query->row;
	}
	
	public function getCategories($parent_id = 0) {
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "category c LEFT JOIN " . DB_PREFIX . "category_description cd ON (c.category_id = cd.category_id) LEFT JOIN " . DB_PREFIX . "category_to_store c2s ON (c.category_id = c2s.category_id) WHERE c.parent_id = '" . (int)$parent_id . "' AND cd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND c2s.store_id = '" . (int)$this->config->get('config_store_id') . "'  AND c.status = '1' ORDER BY c.sort_order, LCASE(cd.name)");
		
		return $query->rows;
	}
	
	public function getTotalCategoriesByCategoryId($parent_id = 0) {
		$query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "category c LEFT JOIN " . DB_PREFIX . "category_to_store c2s ON (c.category_id = c2s.category_id) WHERE c.parent_id = '" . (int)$parent_id . "' AND c2s.store_id = '" . (int)$this->config->get('config_store_id') . "' AND c.status = '1'");
		
		return $query->row['total'];
	}
	
	/**
	 * Custom function to return all banner information associated to this particular category.
	 */
	public function getCategoryBanners($category_id) {
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "category_banner WHERE category_id = '" . (int)$category_id . "'");
		
		return $query->rows;
	}
	
	/**
	 * Returns the minimum price within the category
	 */ 
	 public function getMinimumPriceByCategory($category_id) {		
		$query = $this->db->query("SELECT MIN(p.price) AS min_price FROM " . DB_PREFIX . "product p, " . DB_PREFIX . "product_to_category pc WHERE p.product_id = pc.product_id AND category_id = '" . (int)$category_id . "'");
		
		return $query->row['min_price'];
	 }
	 
	/**
	 * Returns the maximum price within the category
	 */ 
	 public function getMaximumPriceByCategory($category_id) {		
		$query = $this->db->query("SELECT MAX(p.price) AS max_price FROM " . DB_PREFIX . "product p, " . DB_PREFIX . "product_to_category pc WHERE p.product_id = pc.product_id AND category_id = '" . (int)$category_id . "'");
		
		return $query->row['max_price'];
	 }
	 
	 /**
	  * Returns all manufacturer information within the category
	  */ 
	 public function getManufacturersByCategory($category_id) {	
		$query =  $this->db->query("SELECT DISTINCT m.manufacturer_id, m.name FROM " . DB_PREFIX . "manufacturer m, " . DB_PREFIX . "product p WHERE p.manufacturer_id = m.manufacturer_id AND product_id IN (SELECT DISTINCT product_id FROM " . DB_PREFIX . "product_to_category WHERE category_id = '" . (int)$category_id . "')");
		
		return $query->rows;
	 }
	 
	 /**
	  * Returns all vendors/stores information within the category
	  */ 
	 public function getVendorsByCategory($category_id) {	
		$query =  $this->db->query("SELECT DISTINCT v.vendor_id, v.name FROM " . DB_PREFIX . "vendor v, " . DB_PREFIX . "product_to_vendor pv WHERE v.vendor_id = pv.vendor_id AND pv.product_id IN (SELECT DISTINCT product_id FROM " . DB_PREFIX . "product_to_category WHERE category_id = '" . (int)$category_id . "')");
		
		return $query->rows;
	 }

	 /**
	  * Retrieve all the attributes associated with this category.
	  */
	 public function getAttributesOfCategory($category_id) {
		$query =  $this->db->query("SELECT a.attribute_id, a.name, ca.possible_values FROM " . DB_PREFIX . "category_attribute ca, " . DB_PREFIX . "attribute a WHERE ca.attribute_id = a.attribute_id AND category_id = '" . (int)$category_id . "'");
		
		return $query->rows;
	 }
	
}
?>